package com.saic.parse.impl;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import com.saic.parse.IImport;


public class ImportBrandParseAutoHome implements IImport {
	
	public void importExcel(Connection conn)throws Exception {


		
		int flag = 0;
		Statement stmt = conn.createStatement();
		String name = null;
		String desc = null;
		String xcarid = null;
		String autohomeid = null;
		String bitautoid = null;
		String url = null;
		InputStream stream = new FileInputStream("d:/brand_contrast.xls");  
		HSSFWorkbook wb = new HSSFWorkbook(stream);
		Sheet sheet = wb.getSheetAt(0);  
		for(Row row:sheet){
			for(Cell cell:row){
				//主品牌名
				if(cell.getColumnIndex()==0){
					name = cell.toString();
				}
				//来源
				if(cell.getColumnIndex()==1){
					desc = cell.toString();
				}
				//xcarid
				if(cell.getColumnIndex()==2){
					xcarid = cell.toString();
				}
				//autohomeid
				if(cell.getColumnIndex()==3){
					autohomeid = cell.toString();
				}
				//bitautoid
				if(cell.getColumnIndex()==4){
					bitautoid = cell.toString();
				}
				//url
				if(cell.getColumnIndex()==5){
					url = cell.toString();
				}
			}
			conn.setAutoCommit(false);
			ResultSet result = stmt.executeQuery("SELECT auto_increment FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='"+DB_NAME+"' AND TABLE_NAME='t_autohome_brand'");
			result.next();
			int increment = new Integer(result.getString(1));
			if(flag == 0){
				flag = increment;
			}
			try{
				stmt.execute("drop table temp");
			}catch (Exception e) {
				// TODO: handle exception
			}
			// ok
//			stmt.execute("create table temp as (select brand.id from t_autohome_subbrand as subbrand,t_autohome_brand as brand where brand.id = subbrand.brand_id and brand.web_id = '"+autohomeid+"');");
//			ResultSet resultTo = stmt.executeQuery("select * from temp");
//			Integer oldId = null;
//			while(resultTo.next()){
//				oldId= resultTo.getInt(1);
//			}
//			System.out.println(oldId);
			//插入新数据  ok
			java.sql.PreparedStatement pstatement = conn.prepareStatement("insert into t_autohome_brand(`name`,web_id,`desc`,image_url) values('" +name+
					"','" +autohomeid+
					"','" +desc+
					"',?'"+ 
					"')");
			pstatement.setString(1, new String(url.getBytes(),"utf-8"));
			pstatement.execute();
			
//			//更新以前的id
//			stmt.execute("UPDATE t_autohome_subbrand as subbtand set brand_id='" +increment+
//					"' where subbtand.brand_id in (select * from temp)");
//
//			name = null;
//			desc = null;
//			xcarid = null;
//			autohomeid = null;
//			bitautoid = null;
//			url = null;
		}
		//删除老数据
		stmt.execute("delete from t_autohome_brand where id<"+flag);
	}
}
